package com.controller.secretary;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hwpf.HWPFDocument;
import org.apache.poi.hwpf.usermodel.Range;

import com.doc.tools.ziptool;
import com.jfinal.core.ActionKey;
import com.jfinal.core.Controller;
import com.jfinal.kit.PathKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;



public class SumaryKickOpenReportController extends Controller {
	public void index() 
	{
	}
//用于教学秘书开题汇总和选题汇总页面表格的显示
//

	@ActionKey("/secretary-KickOpenReportTable")
	public void KickOpenReportTable(){
		Integer page = getParaToInt("page");
		Integer rows = getParaToInt("rows");
        //获取教学秘书的系的id
		//String secretaryDepart="1";//教学秘书系的id
		String teacherid=getSessionAttr("teacher_id")+"";
		Record teacher = getSecretaryInfo(teacherid);
		String secretaryDepart=teacher.getInt("teacher_department")+"";//教学秘书系的id
		String stuname=null;
		String stuno =null;
		String stuxzb =null;
		String teacherName=null;
		String viewName="keda_secretary_sumary_of_kickopenreport_or_thesistitle";
		stuname= getPara("stuname");
		stuno = getPara("stuno");
		stuxzb = getPara("stuxzb");
		teacherName = getPara("teacher");
		//根据前台给的条件和秘书的系的id 查询对应的数据
		String sql="from "+viewName+" where student_department="+secretaryDepart+" and 1=1";
		if(!"".equals(stuname)&&stuname!=null)
		//if(stuname!=null)
			sql=sql+" and student_name like '%"+stuname+"'"+" or student_name like '"+stuname+"%'"+" or student_name like '%"+stuname+"%'";;
		if(!"".equals(stuno)&&stuno!=null)
		//if(stuno!=null)
			sql=sql+" and student_no='"+stuno+"'";
		if(!"".equals(stuxzb)&&stuxzb!=null)
		//if(stuxzb!=null)
			sql=sql+" and student_xzb='"+stuxzb+"'";
		if(!"".equals(teacherName)&&teacherName!=null)
				sql=sql+" and teacher_name like '%"+teacherName+"'"+" or teacher_name like '"+teacherName+"%'"+" or teacher_name like '%"+teacherName+"%'";
		sql=sql+" ORDER BY student_no asc";
		System.out.println(sql);
		Page<Record> kickopenreport= Db.paginate(page,rows,"select * ",sql);
		Map<String, Object> m = new HashMap<String, Object>();
		m.put("total", kickopenreport.getTotalRow());
		m.put("rows", kickopenreport.getList());
		renderJson(m);
	}
	
	//用于生成教学秘书开题报告页面报告提交情况的饼状图的数据
	//
	@ActionKey("/secretary-OpenReportSubmitCondition")
	public void OpenReportSubmitCondition(){
		//String secretaryDepart="1";//教学秘书系的id
		String teacherid=getSessionAttr("teacher_id")+"";
		Record teacher = getSecretaryInfo(teacherid);
		String secretaryDepart=teacher.getInt("teacher_department")+"";//教学秘书系的id
		
		String viewName="keda_secretary_sumary_of_kickopenreport_or_thesistitle";
		String sql="select count(*) from "+viewName+" where student_department="+secretaryDepart+" and kickoff_report is null";
		Long isnull=Db.queryLong(sql);
		sql="select count(*) from "+viewName+" where student_department="+secretaryDepart+" and kickoff_report is not null";
		Long nonull=Db.queryLong(sql);
		Map<String, Object> m = new HashMap<String, Object>();
		m.put("aa",isnull);
		m.put("bb",nonull);
		renderJson(m);
	}
	
	@ActionKey("/secretary-ThesisTitlePOI")
	public void ThesisTitlePOI(){
		//批量导出
		//条件：
		//秘书对应的系的id
		//年级
		//系所有的班级的集合
		
		String sql;
		//根据表格需求假设需要导出的字段名称为以下几个
		String student_no="";
		String student_name="";
		String thesistitle_name="";
		String teacher_name="";
		String zhiwu_name="";
		String teacher_xueli="";
		//假设秘书系的ID
		//String secretaryDepart="1";//教学秘书系的id
		String teacherid=getSessionAttr("teacher_id")+"";
		Record teacher = getSecretaryInfo(teacherid);
		String secretaryDepart=teacher.getInt("teacher_department")+"";//教学秘书系的id
		//视图名称
		String viewName="keda_sumary_thesistitle_tobe_doc_by_poi";
		//假设年级为2011
		//String grade="2011";
		String grade=teacher.getStr("grade");
		//假设系别为计算机科学与技术系
		//String department="计算机科学与技术系";
		String department=teacher.getStr("department_name");
		//班级的集合
		List<Object> student_xzbList;
		//班级
		String xzb="";
		if("".equals(getPara("xzb"))){
		//一建全系下载
		//获取对应的系所有的班级
    	 sql="select  distinct student_xzb  from "+viewName+" where student_department=? and student_grade=? order by student_xzb asc";
    	 student_xzbList = Db.query(sql,secretaryDepart,grade);
		}else{
		//指导班级下载
		 student_xzbList =new ArrayList<Object>();
		//假设为计算机1班
		xzb=getPara("xzb");
		student_xzbList.add(xzb);
		}
		//按照班级个数循环打印导出
		
		//模板地址
		String sourcepath=null;
		String finalpath="";//临时存放地址
		String zippath="";//压缩文件临时存放地址
		String zipname="";//压缩文件的名字
		//如果student_xzbList.size()大于1，则视为批量下载，需要在服务器打包后再推送给用户
		String currenttime;
		if(student_xzbList.size()>1)
		{	
		currenttime = new SimpleDateFormat("yyyymmddhhmmss").format(new Date());
		finalpath=PathKit.getWebRootPath()+"/WEB-INF/"+"secretarypoidir"+File.separator+currenttime+"选题汇总"+File.separator;
		zippath=PathKit.getWebRootPath()+"/WEB-INF/"+"secretarypoidir"+File.separator;
		zipname=currenttime+"(全系)选题汇总.zip";
		File file = new File(zippath);
		if(!file.exists()){
			file.mkdirs();
		}
		}else if(student_xzbList.size()==1)
			finalpath=PathKit.getWebRootPath()+"/WEB-INF/"+"secretarypoidir"+File.separator;
		File file1 = new File(finalpath);
		if(!file1.exists()){
			file1.mkdirs();
		}
		
		for(int k=0;k<student_xzbList.size();k++)
		{	//System.out.println(student_xzbList.get(k));
		//获取对应班级的学生的选题
		sql="select * from keda_sumary_thesistitle_tobe_doc_by_poi where student_xzb=? ORDER BY student_no asc";
		List<Record> thesisTitleList = Db.find(sql,student_xzbList.get(k));
		Map<String,String> map1 = new HashMap<String,String>();
		//设置表头的系别、班级、年级
		xzb=(String) student_xzbList.get(k);
		map1.put("aa",grade);
		map1.put("aaa",department);
		map1.put("aaaa",xzb);
		
		int size=34;//表格最大记录数,根据模板来确定
		if(thesisTitleList.size()<=34)
			{
			sourcepath=PathKit.getWebRootPath()+"/WEB-INF/"+"secretarymodeldoc"+File.separator+"选题汇总表模板.doc";
			size=34;
			}
		if(thesisTitleList.size()>=35)
		{
			sourcepath=PathKit.getWebRootPath()+"/WEB-INF/"+"secretarymodeldoc"+File.separator+"选题汇总表模板1.doc";
			size=40;
		}
		
		//数据库提取的数据为空，则结束
		if(thesisTitleList.size()<1)
		{
		//	renderJson("{\"result\":"+"false}");
			continue;
		}
		
		//数据是否超过表格模板所能容纳的记录数
		if(thesisTitleList.size()<=size)
		{
		    int j=0;
		    //给map填数据，用于POI导表格的数据
			for(j=0;j<thesisTitleList.size();j++)
			{	
				//对空值进行处理
				if("".equals(thesisTitleList.get(j).getStr("student_no"))||thesisTitleList.get(j).getStr("student_no")==null)
					student_no=" ";
				else
					student_no=thesisTitleList.get(j).getStr("student_no").trim();
				if("".equals(thesisTitleList.get(j).getStr("student_name"))||thesisTitleList.get(j).getStr("student_name")==null)
					student_name=" ";
				else
					student_name=thesisTitleList.get(j).getStr("student_name").trim();;
				if("".equals(thesisTitleList.get(j).getStr("thesistitle_name"))||thesisTitleList.get(j).getStr("thesistitle_name")==null)
					thesistitle_name=" ";
				else
					thesistitle_name=thesisTitleList.get(j).getStr("thesistitle_name").trim();
				if("".equals(thesisTitleList.get(j).getStr("teacher_name"))||thesisTitleList.get(j).getStr("teacher_name")==null)
					teacher_name=" ";
				else
					teacher_name=thesisTitleList.get(j).getStr("teacher_name").trim();
				if("".equals(thesisTitleList.get(j).getStr("zhiwu_name"))||thesisTitleList.get(j).getStr("zhiwu_name")==null)	
					zhiwu_name=" ";
				else
					zhiwu_name=thesisTitleList.get(j).getStr("zhiwu_name").trim();
				if("".equals(thesisTitleList.get(j).getStr("teacher_xueli"))||thesisTitleList.get(j).getStr("teacher_xueli")==null)
					teacher_xueli=" ";
				else
					teacher_xueli=thesisTitleList.get(j).getStr("teacher_xueli").trim();
				
				//设置表格里面的数据
				map1.put("a"+(j+1),student_no);
				map1.put("b"+(j+1),student_name);
				map1.put("c"+(j+1),thesistitle_name);
				map1.put("d"+(j+1),teacher_name);
				map1.put("e"+(j+1),zhiwu_name);
				map1.put("f"+(j+1),teacher_xueli);
				map1.put("g"+(j+1),"");
			}	
			//数据库的记录数少于表格的总记录数，则给表格模板的多余空格填空白数据
			//j的值最大为size-1,这里是33
			if(thesisTitleList.size()<size&&j>=(thesisTitleList.size()-1))
			{
				for(j=j;j<size;j++)
				{
				map1.put("a"+(j+1)," ");
				map1.put("b"+(j+1)," ");
				map1.put("c"+(j+1)," ");
				map1.put("d"+(j+1)," ");
				map1.put("e"+(j+1)," ");
				map1.put("f"+(j+1)," ");
				map1.put("g"+(j+1)," ");
				}
			}
		}else{
		//	renderJson("{\"result\":"+"false}");
		//return;
			continue;
		}
		
		FileOutputStream out;
		FileInputStream is;
		try {
			//读取表格模板
			is = new FileInputStream(sourcepath);
			HWPFDocument doc = new HWPFDocument(is);
			Range range=doc.getRange();
			for (Map.Entry<String, String> entry : map1.entrySet()) {
				range.replaceText("{"+entry.getKey()+"}", entry.getValue());
				}
			ByteArrayOutputStream ostream = new ByteArrayOutputStream();
			doc.write(ostream);
			//设置表格导出在服务器上的临时存放地址
			out = new FileOutputStream(finalpath+student_xzbList.get(k)+"(班)选题汇总表.doc");
			out.write(ostream.toByteArray());
			out.close();
			is.close();
			
			//用于单个班级导出的时候作为renderFile的参数----------------------------------------------------------------
			if(student_xzbList.size()==1)
				finalpath=finalpath+student_xzbList.get(k)+"(班)选题汇总表.doc";
			//----------------------------------------------------------------
			//renderJson("{\"result\":"+"true}");
			//System.out.println(finalFile);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			//renderJson("{\"result\":"+"false}");
			//out.close();
			//is.close();
			e.printStackTrace();
		}
		
	}
		//--------------------对批量处理的文件进行压缩并返还给用户-------------------------
		if(student_xzbList.size()>1){
			try {
				ziptool.ZIP(finalpath,zippath+zipname);
				System.out.println("111111111111111111111");
				renderFile(new File(zippath+zipname));
				student_xzbList.clear();
				
				//--对缓存文件进行删除处理--------------------
				File f=new File(finalpath);
				if(f.exists())
					{
					if(f.isDirectory())
					{
						File[] files=f.listFiles();
						for(int i=0;i<files.length;i++)
						{
							if(files[i].isFile())
								files[i].delete();
						}
					}
					f.delete();
					//f.deleteOnExit();
					System.out.println("zip.delete()");
			//删除结束-----------------------------------------------------
					}
			} catch (IOException e) {
				// TODO Auto-generated catch block
				student_xzbList.clear();
				//renderJson("{\"result\":"+"false}");
				e.printStackTrace();
			}
		}
		//-----结束---------------对批量处理的文件进行压缩并返还给用户-------------------------
		//单独导出一个班级的文件的时候------------------------------------
				if(student_xzbList.size()==1){
					System.out.println(finalpath);
						renderFile(new File(finalpath));
						student_xzbList.clear();			
				}
		//结束------------------------------------------
	}
	
	@ActionKey("/secretary-GetDepartmentXZB")
	public void GetDepartmentXZB()
	{
		 //假设秘书系的ID
		//String secretaryDepart="1";//教学秘书系的id
		String teacherid=getSessionAttr("teacher_id")+"";
		Record teacher = getSecretaryInfo(teacherid);
		String secretaryDepart=teacher.getInt("teacher_department")+"";//教学秘书系的id
		//视图名称
		String viewName="keda_sumary_thesistitle_tobe_doc_by_poi";
		//假设年级为2011
		//String grade="2011";
		String grade=teacher.getStr("grade");
		String sql="select  distinct student_xzb  from "+viewName+" where student_department=? and student_grade=? order by student_xzb asc";
    	List<Record> student_xzbList = Db.find(sql,secretaryDepart,grade);
    	renderJson(student_xzbList);
	}
	
	public Record getSecretaryInfo(String teacherid){
		//max(student_grade)获取数据库中最大的年级
		String viewName="keda_secretary_tch_stu_department_info";
		String sql="SELECT max(student_grade) as grade,teacher_department,department_name from "+viewName+" where teacher_id=?";
		return Db.findFirst(sql,teacherid);
	}
}
